package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBOfferLabour implements IFDBOfferLabour{
	private Connection connection;
	private int offerLabourId1;
	
	public DBOfferLabour()
	{
		connection = DbConnection.getInstance().getDBcon();
	}
	
	
	
	public ArrayList<OfferLabour> getAllOfferLabours() 
	{
		return miscWhere("");
	}
	
	public ArrayList<OfferLabour> getAllOfferLaboursConnectedToOffer(int offerId)
	{
		return  miscWhere("offerId="+offerId);
	}
	
	
	
	public OfferLabour findOfferLabour(int offerId)
    {   String wClause = "  offerId = " + offerId;
        return singleWhere(wClause);
    }
	
	
	
	public OfferLabour findOfferLabourById(int offerLabourId)
    {   String wClause = "  offerLabourId = " + offerLabourId;
        return singleWhere(wClause);
    }
	


	public int deleteOfferLabour(int offerLabourId) {
		int rc = -1;
		
		String query = "DELETE FROM OfferLabour WHERE offerLabourId = "+offerLabourId;
		System.out.println(query);
		
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the OfferLabour with offerLabour ID "+offerLabourId);
		}
		
		
		return rc;
	}
	
	public int deleteOfferLabourConnected(int offerId) {
		int rc = -1;
		
		String query33 = "DELETE FROM OfferLabour WHERE offerId = "+offerId;
		System.out.println(query33);
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query33);
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the OfferLabour connected with "+offerId);
		}
		
		
		return rc;
	}
	
	public void generateId()
	{
	    offerLabourId1 = GetMax.getMaxId("Select max(offerLabourId) from OfferLabour");
		offerLabourId1 = offerLabourId1 + 1;
       System.out.println("next = " +  offerLabourId1);
	}
	
	public int getGeneratedId()
    {
        return offerLabourId1;
    }
	
	public int insertOfferLabour(OfferLabour offerLabour) throws Exception
    {   
		generateId();
  
       int rc = -1;
	   String query="INSERT INTO OfferLabour(offerId, labourId, price, amount, name, offerLabourId)   VALUES "
				+ "("+offerLabour.getOfferId()+","+offerLabour.getLabourId()+","+offerLabour.getLabourPrice()+","+offerLabour.getLabourAmount()+",'"+offerLabour.getLabourName()+"',"+offerLabourId1+")";

       System.out.println("insert : " + query);
      try{ 
          Statement stmt = connection.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }
       catch(SQLException ex){
          System.out.println("Labour insert Error");
          throw new Exception ("Error");
       }
       return(rc);
    }

	
	public int updateOfferLabour(OfferLabour offerLabour) {
		OfferLabour labourObj  = offerLabour;
		int rc = -1;
		
		String query1 =  "UPDATE OfferLabour SET offerId = "+offerLabour.getOfferId() +", labourId="+offerLabour.getLabourId()+", price = "+offerLabour.getLabourPrice()+", amount = "+offerLabour.getLabourAmount()+", offerLabourId ="+offerLabour.getOfferLabourId()+
		          " WHERE offerLabourId = "+ labourObj.getOfferLabourId();
		System.out.println(query1);
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't update"+query1);
		}
		
		
		return rc;
	}
	

	
	private OfferLabour singleWhere(String wClause)
	{
		OfferLabour offerlabour = new OfferLabour();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
				offerlabour = buildOfferlabour(results);
			
			stmt.close();
		}
		catch(Exception ex){}
		
		return offerlabour;
	}
	

	
	private ArrayList<OfferLabour> miscWhere(String wClause)
	{
		ArrayList<OfferLabour> offerLabours = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			
			while(results.next())
			{
				OfferLabour offerlabour = buildOfferlabour(results);
				offerLabours.add(offerlabour);
			}
			
			stmt.close();
			
		}
		catch(Exception ex){}
		return offerLabours;
	}
	
    
	
	
	
	 private OfferLabour buildOfferlabour(ResultSet results){
	        OfferLabour offerlabourObj = new OfferLabour();
	          try{
	                offerlabourObj.setOfferId(results.getInt("offerId"));
	                offerlabourObj.setLabourId(results.getInt("labourId"));
	                offerlabourObj.setLabourPrice(results.getDouble("price"));
	                offerlabourObj.setLabourAmount(results.getInt("amount"));
	                offerlabourObj.setLabourName(results.getString("name"));
	                offerlabourObj.setOfferLabourId(results.getInt("offerLabourId"));
	          }
	         catch(Exception e)
	         {
	             System.out.println("Cannot create OfferLabour object.");
	         }
	         return offerlabourObj;
	      
	    }
	
	
	private String buildQuery(String wClause)
	{
		String query = "SELECT offerId, labourId, price, amount, name, offerLabourId FROM OfferLabour";
		
		if(wClause.length() > 0) query = query +" WHERE "+ wClause;
		
		return query;
	}



}
